﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DBUtil;
using ZXCompany.Models.Entity;

namespace ZXCompany.Models.Dao
{
    public class ZX_UserDao
    {
        private Database db = new Database();
        private List<ZX_User> GetListFromDT(DataTable dt)
        {
            List<ZX_User> ltrain = new List<ZX_User>();

            foreach (DataRow dr in dt.Rows)
            {
                ZX_User user = new ZX_User();
                user.ID = Convert.ToInt32(dr["id"]);
                user.usercode = dr["usercode"].ToString();
                user.username = dr["username"].ToString();
                user.password = dr["password"].ToString();
                user.post = dr["post"].ToString();
                user.status = dr["status"].ToString();
                user.remark = dr["remark"].ToString();
                user.addUserID =Convert.ToInt32(dr["addUserID"]);
                user.addTime = dr["addTime"].ToString();
                user.updateTime = dr["updateTime"].ToString();
                ltrain.Add(user);                
            }
            return ltrain;
        }
        /// <summary>
        /// 选择培训系列
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<ZX_User> Select(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(id,0) as id,
                                isnull(usercode,'') as usercode,
                                isnull(username,'') as username,
                                isnull(password,'') as password,
                                isnull(post,'') as post,
                                isnull(status,'') as status,
                                isnull(remark,'') as remark,
                                isnull(addUserID,0) as addUserID,
                                isnull(addTime,'') as addTime,
                                isnull(updateTime,'') as updateTime
                            FROM [ZX_User]";
                DataTable dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过id找到用户
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public ZX_User SelectByID(int id, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(id,0) as id,
                                isnull(usercode,'') as usercode,
                                isnull(username,'') as username,
                                isnull(password,'') as password,
                                isnull(post,'') as post,
                                isnull(status,'') as status,
                                isnull(remark,'') as remark,
                                isnull(addUserID,0) as addUserID,
                                isnull(addTime,'') as addTime,
                                isnull(updateTime,'') as updateTime
                              FROM [ZX_User] where [ID]=@ID";
                SqlParameter[] spvalues = new SqlParameter[] { 
                new SqlParameter("@ID",id)
                };
                DataTable dt = db.Squery(sql, sconn, spvalues);
                List<ZX_User> ltrain = GetListFromDT(dt);
                return ltrain.Count > 0 ? ltrain[0] : new ZX_User();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过code编码找到用户
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public ZX_User SelectByCode(string ucode, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(id,0) as id,
                                isnull(usercode,'') as usercode,
                                isnull(username,'') as username,
                                isnull(password,'') as password,
                                isnull(post,'') as post,
                                isnull(status,'') as status,
                                isnull(remark,'') as remark,
                                isnull(addUserID,0) as addUserID,
                                isnull(addTime,'') as addTime,
                                isnull(updateTime,'') as updateTime
                              FROM [ZX_User] where [usercode]=@usercode";
                SqlParameter[] spvalues = new SqlParameter[] { 
                    new SqlParameter("@usercode",ucode)
                };
                DataTable dt = db.Squery(sql, sconn, spvalues);
                List<ZX_User> ltrain = GetListFromDT(dt);
                return ltrain.Count > 0 ? ltrain[0] : new ZX_User();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过code编码找到用户
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public ZX_User SelectLogin(string ucode,string upwd, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull(id,0) as id,
                                isnull(usercode,'') as usercode,
                                isnull(username,'') as username,
                                isnull(password,'') as password,
                                isnull(post,'') as post,
                                isnull(status,'') as status,
                                isnull(remark,'') as remark,
                                isnull(addUserID,0) as addUserID,
                                isnull(addTime,'') as addTime,
                                isnull(updateTime,'') as updateTime
                              FROM [ZX_User] where [usercode]=@usercode 
                              and  [password]=@password  ";
                SqlParameter[] spvalues = new SqlParameter[] { 
                    new SqlParameter("@usercode",ucode),
                    new SqlParameter("@password",upwd)
                };
                DataTable dt = db.Squery(sql, sconn, spvalues);
                List<ZX_User> ltrain = GetListFromDT(dt);
                return ltrain.Count > 0 ? ltrain[0] : new ZX_User();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Delete(int typeid, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"delete FROM [ZX_User] where ID=@ID";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@ID",typeid)
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Insert(Entity.ZX_User zxei, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"insert into ZX_User 
                              (usercode,
                                username,
                                password,
                                post,
                                status,
                                remark,
                                addUserID,
                                addTime) values 
                              ( @usercode,
                                @username,
                                @password,
                                @post,
                                @status,
                                @remark,
                                @addUserID,
                                @addTime)";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@usercode",Database.SqlNull(zxei.usercode)),
                    new SqlParameter("@username",Database.SqlNull(zxei.username)),
                    new SqlParameter("@password",Database.SqlNull(zxei.password)),
                    new SqlParameter("@post",Database.SqlNull(zxei.post)),
                    new SqlParameter("@status",Database.SqlNull(zxei.status)),
                    new SqlParameter("@remark",Database.SqlNull(zxei.remark)),
                    new SqlParameter("@addUserID",Database.SqlNull(zxei.addUserID)),
                    new SqlParameter("@addTime",Database.SqlNull(zxei.addTime))
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据id获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Update(Entity.ZX_User zxei, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = "update ZX_User set [username]=@username";
                //id,
                //usercode,
                //username,
                //password,
                //post,
                //status,
                //remark,
                //addUserID,
                //addTime,
                //updateTime
                sql += @",[password]=@password
                        ,[post]=@post
                        ,[status]=@status
                        ,[remark]=@remark
                        ,[addUserID]=@addUserID
                        ,[addTime]=@addTime
                        ,[updateTime]=@updateTime
                    FROM [ZX_User] where [ID]=@ID";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@ID",zxei.ID),
                    new SqlParameter("@username",Database.SqlNull(zxei.username)),
                    new SqlParameter("@password",Database.SqlNull(zxei.password)),
                    new SqlParameter("@post",Database.SqlNull(zxei.post)),
                    new SqlParameter("@status",Database.SqlNull(zxei.status)),
                    new SqlParameter("@remark",Database.SqlNull(zxei.remark)),
                    new SqlParameter("@post",Database.SqlNull(zxei.post)),
                    new SqlParameter("@addUserID",Database.SqlNull(zxei.addUserID)),
                    new SqlParameter("@addTime",Database.SqlNull(zxei.addTime)),
                    new SqlParameter("@updateTime",Database.SqlNull(zxei.updateTime))
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}